package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.ndp.fb.rdb.api.EntrustAccountRdbService;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.EntrustAccount;
import com.ndp.fb.rdb.model.EntrustAccountExt;
import com.ndp.fb.rdb.model.Pager;
import com.ndp.fb.redis.api.RedisClient;
import com.ndp.fb.util.DateFormatUtil;
import com.ndp.fb.util.ListUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementCreator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.concurrent.TimeUnit;

/**
 * Created by hunk.qin on 2015/1/27.
 */
@Service
public class EntrustAccountRdbServiceImpl extends DaoTemplate implements EntrustAccountRdbService {

	@Autowired
	private RedisClient redisClient;


    @Override
    public List<EntrustAccount> findAllAdAccount() {
        String sql = "SELECT * FROM entrust_account";
        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(EntrustAccount.class));
    }


    @Override
    public List<EntrustAccount> findAllWithOutAccount() {
        return findAll(DefaultRowMapper.autoRowMapper(EntrustAccount.class));
    }


    @Override
    public String saveAdAccount(EntrustAccount entrustAccount) {
        return saveResultId(entrustAccount).toString();
    }


    @Override
    public Integer updateAdAccount(final EntrustAccount entrustAccount) {
        return getJdbcTemplate().update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                String sql1 = "UPDATE entrust_account SET " +
                        "name = ?,email = ?," +
                        "account_status = ?,time_zone = ?,custom_audience_tos = ?,balance = ?," +
                        "remark = ?,update_time = ?,is_updated_resource=?" +
                        " WHERE fb_account_id =?";
                String sql2 = "UPDATE entrust_account SET " +
                        "name = ?,email = ?," +
                        "account_status = ?,time_zone = ?,custom_audience_tos = ?,balance = ?," +
                        "remark = ?,update_time = ?" +
                        " WHERE fb_account_id =?";
                PreparedStatement ps = null;
                if(entrustAccount.getIsUpdatedResource() !=null){
                    ps = con.prepareStatement(sql1);
                    ps.setObject(1, entrustAccount.getName());
                    ps.setObject(2, entrustAccount.getEmail());
                    ps.setObject(3, entrustAccount.getAccountStatus());
                    ps.setObject(4, entrustAccount.getTimezone());
                    ps.setObject(5, entrustAccount.getCustomAudienceTos());
                    ps.setObject(6, entrustAccount.getBalance());
                    ps.setObject(7, entrustAccount.getRemark());
                    ps.setObject(8, entrustAccount.getUpdateTime());
                    ps.setObject(9, entrustAccount.getIsUpdatedResource());
                    ps.setObject(10, entrustAccount.getFbAccountId());
                }else{
                    ps = con.prepareStatement(sql2);
                    ps.setObject(1, entrustAccount.getName());
                    ps.setObject(2, entrustAccount.getEmail());
                    ps.setObject(3, entrustAccount.getAccountStatus());
                    ps.setObject(4, entrustAccount.getTimezone());
                    ps.setObject(5, entrustAccount.getCustomAudienceTos());
                    ps.setObject(6, entrustAccount.getBalance());
                    ps.setObject(7, entrustAccount.getRemark());
                    ps.setObject(8, entrustAccount.getUpdateTime());
                    ps.setObject(9, entrustAccount.getFbAccountId());
                }
                return ps;
            }
        });
    }

    @Override
    public void updateAgreeCustomAudience(Long accountId)
    {
        String sql = "UPDATE entrust_account SET custom_audience_tos = 1 WHERE 1=1 AND fb_account_id = ?";
        getJdbcTemplate().update(sql, new Object[]{accountId});
    }

    @Override
    public Integer batchUpdateAdAccount(final List<EntrustAccount> entrustAccounts) {
        if (ListUtil.isEmpty(entrustAccounts)) return null;
        String sql = "UPDATE entrust_account SET " +
                " name = ?,email = ?," +
                " account_status = ?,time_zone = ?,custom_audience_tos = ?,balance = ?," +
                " remark = ?,update_time = ? " +
                " WHERE fb_account_id =?";
        int[] tempArry = getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                EntrustAccount entrustAccount = entrustAccounts.get(i);
                ps.setObject(1, entrustAccount.getName());
                ps.setObject(2, entrustAccount.getEmail());
                ps.setObject(3, entrustAccount.getAccountStatus());
                ps.setObject(4, entrustAccount.getTimezone());
                ps.setObject(5, entrustAccount.getCustomAudienceTos());
                ps.setObject(6, entrustAccount.getBalance());
                ps.setObject(7, entrustAccount.getRemark());
                ps.setObject(8, entrustAccount.getUpdateTime());
                ps.setObject(9, entrustAccount.getFbAccountId());
            }

            @Override
            public int getBatchSize() {
                return entrustAccounts.size();
            }
        });
        int flag = 1;
        for (int i : tempArry) {
            if (i == 0) {
                flag = 0;
                break;
            }
        }
        return flag;

    }


    @Override
    public Integer batchSaveAccounts(final List<EntrustAccount> entrustAccounts) {
        String sql = "INSERT INTO entrust_account (fb_account_id,name,email," +
                "account_status,time_zone,custom_audience_tos,balance,remark,create_time,update_time,is_delete) " +
                " VALUES(?,?,?,?,?,?,?,?,?,?,?)";
        getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                EntrustAccount entrustAccount = entrustAccounts.get(i);
                ps.setObject(1, entrustAccount.getFbAccountId());
                ps.setObject(2, entrustAccount.getName());
                ps.setObject(3, entrustAccount.getEmail());
                ps.setObject(4, entrustAccount.getAccountStatus());
                ps.setObject(5, entrustAccount.getTimezone());
                ps.setObject(6, entrustAccount.getCustomAudienceTos());
                ps.setObject(7, entrustAccount.getBalance());
                ps.setObject(8, entrustAccount.getRemark());
                ps.setObject(9, DateFormatUtil.formatLong19(entrustAccount.getCreateTime()));
                ps.setObject(10, DateFormatUtil.formatLong19(entrustAccount.getUpdateTime()));
                ps.setObject(11, 0);  //初始化为 0
            }

            @Override
            public int getBatchSize() {
                return entrustAccounts.size();
            }
        });
        return 1;
    }


    @Override
    public EntrustAccount findbyEntrustAccountId(Long entrustAccountId, Integer isDelele) {
        String sql = "SELECT * FROM entrust_account WHERE fb_account_id= ? ";
        EntrustAccount entrustAccount = findOne(sql,new Object[]{entrustAccountId},DefaultRowMapper.autoRowMapper(EntrustAccount.class));
        return entrustAccount;
    }

    @Override
    public List<EntrustAccountExt> findByConnectionObjectIdAndUserId(Long fbConnectionObjectId, String userId) {
    	String sql = "SELECT DISTINCT "
    			+"entrust_account.id,"
    			+"fb_user.fb_user_id,"
    			+"fb_user.user_id,"
    			+"fb_userpermissions_adaccounts.role,"
    			+"fb_userpermissions_adaccounts.fb_business_id,"
    			+"connection_object.fb_connection_object_id,"
    			+"entrust_account.fb_account_id,"
    			+"entrust_account.`name`,"
    			+"entrust_account.email,"
    			+"entrust_account.account_status,"
    			+"entrust_account.time_zone,"
    			+"entrust_account.custom_audience_tos,"
    			+"entrust_account.balance,"
    			+"entrust_account.remark,"
    			+"entrust_account.create_time,"
    			+"entrust_account.update_time,"
    			+"entrust_account.is_delete,"
    			+"entrust_account.is_optimize,"
    			+"entrust_account.is_updated_resource,"
    			+"fb_userpermissions_adaccounts.`status` "
    			+"FROM "
    			+"fb_user "
    			+"INNER JOIN fb_userpermissions_adaccounts ON fb_user.fb_user_id = fb_userpermissions_adaccounts.fb_user_id "
    			+"INNER JOIN entrust_account_connection_objects ON fb_userpermissions_adaccounts.fb_account_id = entrust_account_connection_objects.fb_account_id "
    			+"INNER JOIN connection_object ON entrust_account_connection_objects.fb_connection_object_id = connection_object.fb_connection_object_id "
    			+"INNER JOIN entrust_account ON entrust_account_connection_objects.fb_account_id = entrust_account.fb_account_id "
    			+"WHERE connection_object.type = 2 AND fb_userpermissions_adaccounts.role IN ('ADMIN','GENERAL_USER') and fb_userpermissions_adaccounts.status ='ACTIVE' AND connection_object.fb_connection_object_id = ? "
    			+"AND fb_user.user_id = ?";

        Object[] params = new Object[2];
        params[0] = fbConnectionObjectId;
        params[1] = userId;
        return getJdbcTemplate().query(sql, params, DefaultRowMapper.autoRowMapper(EntrustAccountExt.class));
    }


    @Override
    public Integer batchDeleteAccounts(final List<EntrustAccount> entrustAccounts) {
        String sql = "delete from entrust_account where fb_account_id = ?";
        getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                EntrustAccount entrustAccount = entrustAccounts.get(i);
                ps.setObject(1, entrustAccount.getFbAccountId());
            }

            @Override
            public int getBatchSize() {
                return entrustAccounts.size();
            }
        });
        return 1;
    }

    @Override
    public EntrustAccount findByFbAccountId(Long fbAccountId) {
        return findOneByProperty("fb_account_id", fbAccountId, DefaultRowMapper.autoRowMapper(EntrustAccount.class));
    }



	/**
	 * @discription
	 * @author giser.yang
	 * 创建时间 2015年6月3日 下午5:45:39
	 * @param userId
	 * @param offset
	 * @param limit
	 * @return
	 */
	@Override
	public Pager<EntrustAccountExt> findEntrustAccountsPagerByUserId(
			String userId,Long fbBusinessId,  Integer offset, Integer limit) {
		String sql = "SELECT fb_userpermissions_adaccounts.fb_business_id,fb_userpermissions_adaccounts.role,entrust_account.id,entrust_account.fb_account_id,entrust_account.`name`,entrust_account.email,entrust_account.account_status,entrust_account.time_zone,entrust_account.custom_audience_tos,entrust_account.balance,fb_user.fb_user_id,entrust_account.remark,entrust_account.create_time,entrust_account.update_time,entrust_account.is_delete,entrust_account.is_optimize,entrust_account.is_updated_resource,fb_userpermissions_adaccounts.`status` FROM entrust_account INNER JOIN fb_userpermissions_adaccounts ON fb_userpermissions_adaccounts.fb_account_id = entrust_account.fb_account_id INNER JOIN fb_user ON fb_user.fb_user_id = fb_userpermissions_adaccounts.fb_user_id";
		sql = sql+" where fb_user.user_id ='"+userId+"' and fb_userpermissions_adaccounts.fb_business_id = "+fbBusinessId+" and fb_userpermissions_adaccounts.status = 'ACTIVE'";
	    return findPager(sql, null, offset, limit, DefaultRowMapper.autoRowMapper(EntrustAccountExt.class));
	}


	/**
	 * @discription
	 * @author giser.yang
	 * 创建时间 2015年6月3日 下午6:01:56
	 * @param userId
	 * @return
	 * @see com.ndp.fb.rdb.api.EntrustAccountRdbService#findAdAccountListByUserIdAll(java.lang.String)
	 */
	@Override
	public List<EntrustAccountExt> findAdAccountListByUserIdAll(String userId) {
		// TODO Auto-generated method stub
		String sql = "SELECT fb_userpermissions_adaccounts.fb_business_id,fb_userpermissions_adaccounts.role,entrust_account.id,entrust_account.fb_account_id,entrust_account.`name`,entrust_account.email,entrust_account.account_status,entrust_account.time_zone,entrust_account.custom_audience_tos,entrust_account.balance,fb_user.fb_user_id,entrust_account.remark,entrust_account.create_time,entrust_account.update_time,entrust_account.is_delete,entrust_account.is_optimize,entrust_account.is_updated_resource,fb_userpermissions_adaccounts.`status` FROM entrust_account INNER JOIN fb_userpermissions_adaccounts ON fb_userpermissions_adaccounts.fb_account_id = entrust_account.fb_account_id INNER JOIN fb_user ON fb_user.fb_user_id = fb_userpermissions_adaccounts.fb_user_id";
		sql = sql+" where fb_user.user_id = ? and fb_userpermissions_adaccounts.status = 'ACTIVE'";

		return getJdbcTemplate().query(sql, new Object[]{userId}, DefaultRowMapper.autoRowMapper(EntrustAccountExt.class));
	}

    /**
     * @discription
     * @author giser.yang
     * 创建时间 2015年6月3日 下午6:01:56
     * @param userId
     * @return
     * @see com.ndp.fb.rdb.api.EntrustAccountRdbService#findAdAccountListByUserIdAll(java.lang.String)
     */
    @Override
    public List<EntrustAccountExt> findAdAccountListByUserId(String userId) {
        // TODO Auto-generated method stub
        String sql = "SELECT fb_userpermissions_adaccounts.fb_business_id,fb_userpermissions_adaccounts.role,entrust_account.id,entrust_account.fb_account_id,entrust_account.`name`,entrust_account.email,entrust_account.account_status,entrust_account.time_zone,entrust_account.custom_audience_tos,entrust_account.balance,fb_user.fb_user_id,entrust_account.remark,entrust_account.create_time,entrust_account.update_time,entrust_account.is_delete,entrust_account.is_optimize,entrust_account.is_updated_resource,fb_userpermissions_adaccounts.`status` FROM entrust_account INNER JOIN fb_userpermissions_adaccounts ON fb_userpermissions_adaccounts.fb_account_id = entrust_account.fb_account_id INNER JOIN fb_user ON fb_user.fb_user_id = fb_userpermissions_adaccounts.fb_user_id";
        sql = sql+" where fb_user.user_id = ? and fb_userpermissions_adaccounts.status = 'ACTIVE' AND fb_userpermissions_adaccounts.role IN ('GENERAL_USER', 'ADMIN')";
        return getJdbcTemplate().query(sql, new Object[]{userId}, DefaultRowMapper.autoRowMapper(EntrustAccountExt.class));
    }



    /**
	 * @discription
	 * @author giser.yang
	 * 创建时间 2015年6月9日 上午11:35:34
	 * @return
	 */
	@Override
	public List<EntrustAccount> findAdAccountListByRoles(String... roles) {
		if(roles==null||roles.length==0){
			return null;
		}
		StringBuffer str = new StringBuffer();
		str.append("(");
		for(String role:roles){
			str.append("'");
			str.append(role).append("'").append(",");
		}
		str = new StringBuffer(str.substring(0, str.length()-1));
		str.append(")");
		String sql = "SELECT * from entrust_account WHERE fb_account_id in (SELECT fb_account_id FROM fb_userpermissions_adaccounts WHERE status='ACTIVE' AND role in "+str+")";;
		return getJdbcTemplate().query(sql,DefaultRowMapper.autoRowMapper(EntrustAccount.class));
	}


	/**
	 * @discription
	 * @author giser.yang
	 * 创建时间 2015年6月11日 下午5:10:05
	 * @param userId
	 * @param businessId
	 * @return
	 * @see com.ndp.fb.rdb.api.EntrustAccountRdbService#findAdAccountListByUserIdAndBusinessIdAll(java.lang.String, java.lang.Long)
	 */
	@Override
	public List<EntrustAccountExt> findAdAccountListByUserIdAndBusinessIdAll(
			String userId, Long businessId) {
		// TODO Auto-generated method stub
		String sql = "SELECT fb_userpermissions_adaccounts.fb_business_id,fb_userpermissions_adaccounts.role,entrust_account.id,entrust_account.fb_account_id,entrust_account.`name`,entrust_account.email,entrust_account.account_status,entrust_account.time_zone,entrust_account.custom_audience_tos,entrust_account.balance,fb_user.fb_user_id,entrust_account.remark,entrust_account.create_time,entrust_account.update_time,entrust_account.is_delete,entrust_account.is_optimize,entrust_account.is_updated_resource,fb_userpermissions_adaccounts.`status` FROM entrust_account INNER JOIN fb_userpermissions_adaccounts ON fb_userpermissions_adaccounts.fb_account_id = entrust_account.fb_account_id INNER JOIN fb_user ON fb_user.fb_user_id = fb_userpermissions_adaccounts.fb_user_id";
		sql = sql+" where fb_user.user_id = ? and fb_userpermissions_adaccounts.fb_business_id = ? and fb_userpermissions_adaccounts.status = 'ACTIVE'";
		return getJdbcTemplate().query(sql, new Object[]{userId,businessId},DefaultRowMapper.autoRowMapper(EntrustAccountExt.class));
	}


	/**
	 * @discription 已经增加了redis乐观锁不会重复入库
	 * @author giser.yang
	 * 创建时间 2015年6月18日 下午4:00:23
	 * @return
	 */
	@Override
	public Long saveorupdateAdaccount(EntrustAccount entrustAccount) {
		String key = "EntrustAccount"+entrustAccount.getFbAccountId();
		if(!redisClient.setNx(key, entrustAccount)){
			return 0L;
		}
		try {
			redisClient.expire(key, 5, TimeUnit.SECONDS.name());
			EntrustAccount entrustAccount1 = this.findByFbAccountId(entrustAccount.getFbAccountId());
			if(entrustAccount1 !=null ){
				this.updateAdAccount(entrustAccount);
				return entrustAccount1.getId();
			}else{
				this.saveAdAccount(entrustAccount);
			}
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		}finally{
			redisClient.delete(key);
		}
		return null;
	}


	/**
	 * @discription
	 * @author giser.yang
	 * 创建时间 2015年6月22日 下午10:18:37
	 * @param fbUserId
	 * @return
	 * @see com.ndp.fb.rdb.api.EntrustAccountRdbService#findAdAccountListByFBUserIdAll(java.lang.Long)
	 */
	@Override
	public List<EntrustAccountExt> findAdAccountListByFBUserIdAll(Long fbUserId) {
		// TODO Auto-generated method stub
		String sql = "SELECT fb_userpermissions_adaccounts.fb_business_id,fb_userpermissions_adaccounts.role,entrust_account.id,entrust_account.fb_account_id,entrust_account.`name`,entrust_account.email,entrust_account.account_status,entrust_account.time_zone,entrust_account.custom_audience_tos,entrust_account.balance,fb_user.fb_user_id,entrust_account.remark,entrust_account.create_time,entrust_account.update_time,entrust_account.is_delete,entrust_account.is_optimize,entrust_account.is_updated_resource,fb_userpermissions_adaccounts.`status` FROM entrust_account INNER JOIN fb_userpermissions_adaccounts ON fb_userpermissions_adaccounts.fb_account_id = entrust_account.fb_account_id INNER JOIN fb_user ON fb_user.fb_user_id = fb_userpermissions_adaccounts.fb_user_id";
		sql = sql+" where fb_user.fb_user_id = ? and fb_userpermissions_adaccounts.status = 'ACTIVE'";

		return getJdbcTemplate().query(sql, new Object[]{fbUserId},DefaultRowMapper.autoRowMapper(EntrustAccountExt.class));
	}


	/**
	 * @discription
	 * @author giser.yang
	 * 创建时间 2015年6月22日 下午10:21:41
	 * @param fbUserId
	 * @param businessId
	 * @return
	 * @see com.ndp.fb.rdb.api.EntrustAccountRdbService#findAdAccountListByFBUserIdAndBusinessIdAll(java.lang.Long, java.lang.Long)
	 */
	@Override
	public List<EntrustAccountExt> findAdAccountListByFBUserIdAndBusinessIdAll(
			Long fbUserId, Long businessId) {
		// TODO Auto-generated method stub
		String sql = "SELECT fb_userpermissions_adaccounts.fb_business_id,fb_userpermissions_adaccounts.role,entrust_account.id,entrust_account.fb_account_id,entrust_account.`name`,entrust_account.email,entrust_account.account_status,entrust_account.time_zone,entrust_account.custom_audience_tos,entrust_account.balance,fb_user.fb_user_id,entrust_account.remark,entrust_account.create_time,entrust_account.update_time,entrust_account.is_delete,entrust_account.is_optimize,entrust_account.is_updated_resource,fb_userpermissions_adaccounts.`status` FROM entrust_account INNER JOIN fb_userpermissions_adaccounts ON fb_userpermissions_adaccounts.fb_account_id = entrust_account.fb_account_id INNER JOIN fb_user ON fb_user.fb_user_id = fb_userpermissions_adaccounts.fb_user_id";
		sql = sql+" where fb_user.fb_user_id = ? and fb_userpermissions_adaccounts.fb_business_id = ? and fb_userpermissions_adaccounts.status = 'ACTIVE'";

		return getJdbcTemplate().query(sql, new Object[]{fbUserId,businessId},DefaultRowMapper.autoRowMapper(EntrustAccountExt.class));
	}

    @Override
    public List<EntrustAccount> findNeedUploadResource(Long orgId) {
        String sql = "SELECT\n" +
                "	ea.*\n" +
                "FROM\n" +
                "	`user` u,\n" +
                "	fb_userpermissions_adaccounts fua,\n" +
                "	fb_user fuser,\n" +
                "	entrust_account ea\n" +
                "WHERE\n" +
                "	u.ticket = fuser.user_id\n" +
                "AND fua.fb_user_id = fuser.fb_user_id\n" +
                "AND ea.fb_account_id = fua.fb_account_id\n" +
                "AND fua.role IN ('GENERAL_USER', 'ADMIN')\n" +
                "AND fua. STATUS = 'ACTIVE'\n" +
                "AND u.org_id = ?";
        return getJdbcTemplate().query(sql,new Object[]{orgId},DefaultRowMapper.autoRowMapper(EntrustAccount.class));
    }


    @Override
    public List<EntrustAccount> findAdAccountListByOrgId(Long orgId) {
        String sql = "SELECT\n" +
                "	ea.*\n" +
                "FROM\n" +
                "	`user` u,\n" +
                "	fb_userpermissions_adaccounts fua,\n" +
                "	fb_user fuser,\n" +
                "	entrust_account ea\n" +
                "WHERE\n" +
                "	u.ticket = fuser.user_id\n" +
                "AND fua.fb_user_id = fuser.fb_user_id\n" +
                "AND ea.fb_account_id = fua.fb_account_id\n" +
                "AND u.org_id = ?\n" +
                "AND fua.`status`='ACTIVE'";
        return getJdbcTemplate().query(sql,new Object[]{orgId},DefaultRowMapper.autoRowMapper(EntrustAccount.class));
    }


    /**
     * @discription
     * @author giser.yang
     * 创建时间 2015年8月12日 下午10:05:50
     * @param accountId
     * @return
     * @see com.ndp.fb.rdb.api.EntrustAccountRdbService#findFromCahe(java.lang.Long)
     */
    @Override
    public EntrustAccount findFromCahe(Long accountId) {
        EntrustAccount entrustAccount = (EntrustAccount)redisClient.get("fb_account"+accountId);
        if(entrustAccount==null){
            entrustAccount = this.findByFbAccountId(accountId);
            if(entrustAccount!=null){
                redisClient.set("fb_account"+accountId, entrustAccount);
            }
        }
        return entrustAccount;
    }


    @Override
    public List<EntrustAccount> getPagesByUserId(Long pageId, Long orgId) {
        String sql = "SELECT DISTINCT\n" +
                "	DISTINCT acc.*\n" +
                "FROM\n" +
                "	entrust_account acc,\n" +
                "	fb_userpermissions_pages fup,\n" +
                "	fb_userpermissions_adaccounts fua,\n" +
                "	fb_user fuser,\n" +
                "	`user` u\n" +
                "WHERE\n" +
                "	fup.fb_business_id = fua.fb_business_id\n" +
                "AND fuser.fb_user_id = fua.fb_user_id\n" +
                "AND fuser.user_id = u.ticket\n" +
                "AND acc.fb_account_id = fua.fb_account_id\n" +
                "AND fup.`status` = 'ACTIVE'\n" +
                "AND fua.`status` = 'ACTIVE'\n" +
                "AND fup.role IN ('MANAGER','ADVERTISER','MODERATOR','CONTENT_CREATOR')\n" +
                "AND fua.role IN ('ADMIN', 'GENERAL_USER')\n" +
                "AND fup.page_id = ?\n" +
                "AND u.org_id = ?";
        return getJdbcTemplate().query(sql,new Object[]{pageId,orgId},DefaultRowMapper.autoRowMapper(EntrustAccount.class));
    }

    @Override
    public List<EntrustAccountExt> findAdAccountListByUserIdAndConnObjId(String ticket, Long connObjId) {
        String sql = "SELECT fb_userpermissions_adaccounts.fb_business_id,fb_userpermissions_adaccounts.role,entrust_account.id,entrust_account.fb_account_id,entrust_account.`name`,entrust_account.email,entrust_account.account_status,entrust_account.time_zone,entrust_account.custom_audience_tos,entrust_account.balance,fb_user.fb_user_id,entrust_account.remark,entrust_account.create_time,entrust_account.update_time,entrust_account.is_delete,entrust_account.is_optimize,entrust_account.is_updated_resource,fb_userpermissions_adaccounts.`status` FROM entrust_account INNER JOIN fb_userpermissions_adaccounts ON fb_userpermissions_adaccounts.fb_account_id = entrust_account.fb_account_id INNER JOIN fb_user ON fb_user.fb_user_id = fb_userpermissions_adaccounts.fb_user_id"
                +" where fb_user.user_id = ? and fb_userpermissions_adaccounts.status = 'ACTIVE' AND entrust_account.`fb_account_id` IN "
                +" (SELECT DISTINCT " +
                "  fb_account_id" +
                "  FROM" +
                "    ad_publish_condition AS apc," +
                "    ad_publish_condition_detail_account AS apcda" +
                "  WHERE `user_id` = ?" +
                "    AND `connection_object_id` = ?" +
                "    AND apc.`id` = apcda.`condition_id`)";

        return getJdbcTemplate().query(sql, new Object[]{ticket, ticket, connObjId},DefaultRowMapper.autoRowMapper(EntrustAccountExt.class));
    }
}
